package com.example.studyplanning.mapper;

import com.example.studyplanning.entity.Tips;
import org.apache.ibatis.annotations.*;

import java.util.List;

@Mapper
public interface TipsMapper {
    //1.1展开所有公开心得，输入心得名称进行模糊查询，得到心得信息
    @Select("select tips.*,userName,DATE_FORMAT(tipsTime,'%m-%d') as date_time from tips join user on tips.userID=user.userID where tips.tipsType=1 and tips.tipsTitle like '%${tipsTitle}%' order by tipsTime DESC")
    List<Tips> FindAllTips(String tipsTitle);

    //1.3展示我的心得:输入心得ID通过用户ID心得表和用户表连接查询所有心得信息,通过心得填写时间进行排序
    @Select("select tips.*,userName,DATE_FORMAT(tipsTime,'%Y-%m-%d') as date_time from tips join user on tips.userID=user.userID where tipsID=#{tipsID}")
    List<Tips> findAllTips(Integer tipsID);

    //1.9添加心得：输入心得名称，内容和类型进行添加
    @Insert("insert into tips(tipsTitle,tipsContent,tipsType,userID,likes) values(#{tipsTitle},#{tipsContent},#{tipsType},#{userID},#{likes})")
    Integer insertTips(Tips tips);

    //1.4点赞，根据所选的心得获得心得ID进行点赞
    @Update("update tips set likes=likes+1 where tipsID=#{tipsID}")
    Integer UpdateTipsLike(Tips tips);

    //撤回点赞
    @Update("update tips set likes=likes-1 where tipsID=#{tipsID}")
    Integer UpdateLike(Tips tips);

    //5.2 展示我的心得 输入用户ID得到相关信息
    @Select("select tips.*,userName,DATE_FORMAT(tipsTime,'%Y-%m-%d') as date_time from tips join user on tips.userID=user.userID where tips.userID=#{userID}")
    List<Tips> SelectTipsByID(Integer userID);

    //1.2点赞，根据所选心得得到心得ID得到点赞数
    @Select("select tips.likes from tips where tipsID=#{tipsID}")
    Integer SelectLikes(Integer tipsID);

    //（后台）3.4心得删除
    @Delete("delete from tips where tipsID=#{tipsID}")
    Integer DelByID(Integer tipsID);

    //（后台） 3.1心得查询，根据所输入信息多条件查询
    @Select("select t1.*,user.userName from (select tips.*, date(tipsTime) as date_time from tips) t1 join user on t1.userID=user.userID where date_format(date_time,'%Y-%m-%d') = '${date_time}' and tipsID like '%${tipsID}' and tipsTitle like '%${tipsTitle}' and tipsType like '%${tipsType}' and tipsContent like '%${tipsContent}' and userName like '%${userName}%'")
    List<Tips> SelectTips(@Param("date_time") String date_time, @Param("tipsID") Integer tipsID, @Param("tipsTitle") String tipsTitle, @Param("tipsType") Integer tipsType, @Param("tipsContent") String tipsContent, @Param("userName") String userName);

    //(辅助3.1查询）日期为空时的查询
    @Select("select user.userName,substring(tipsContent,1,6) as content,tips.*,date_format(tipsTime,'%Y-%m-%d') as date_time from tips join user on tips.userID=user.userID where tipsID like '%${tipsID}' and tipsTitle like '%${tipsTitle}' and tipsType like '%${tipsType}' and tipsContent like '%${tipsContent}' and userName like '%${userName}%'")
    List<Tips> SelectTheTips(@Param("tipsID") Integer tipsID, @Param("tipsTitle") String tipsTitle, @Param("tipsType") Integer tipsType, @Param("tipsContent") String tipsContent,@Param("userName") String userName);

    @Select("SELECT date_format(tipsTime,'%Y-%m-%d') tipsDate,count(*) as tipsum  FROM tips group BY date_format(tipsTime,'%Y-%m-%d') order BY date_format(tipsTime,'%Y-%m-%d')")
    List<Tips> CountDayTip();

}